package MySQLConnection;

import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.LinkedList;
import java.util.List;
import java.util.UUID;

import model.Criterion;

import common.Enums.ComponentType;
import common.Enums.DateRange;
import common.Exceptions.DAOException;

import DAO.CriterionDAO;

public class MySQLCriterionDAO implements CriterionDAO 
{
	private final static String TABLE_NAME = "criterion"; 
	private static MySQLCriterionDAO instance;
	
	public static MySQLCriterionDAO getInstance()
	{
		if(instance == null)
			instance = new MySQLCriterionDAO();
		
		return instance;
	}
	
	@Override
	public void insert(Criterion item) throws DAOException 
	{
		String query = "INSERT INTO " + MySqlDAOFactory.DBName + "." + TABLE_NAME + "(crt_id, crt_name, crt_description, crt_goal, crt_solutionImpact, crt_reference, crt_createdDate, crt_lastUpdated)" +
		"VALUES(?, ?, ?, ?, ?, ?, ?, ?);";

		try 
		{
			PreparedStatement preStatement = MySqlDAOFactory.getInstance().prepareStatement(query);
			preStatement.setString(1, item.getID().toString());
			preStatement.setString(2, item.getName());
			preStatement.setString(3, item.getDescription());
			preStatement.setString(4, item.getGoal());
			preStatement.setString(5, item.getSolutionImpact());
			preStatement.setString(6, item.getReference());
			preStatement.setTimestamp(7, new java.sql.Timestamp(new java.util.Date().getTime()));
			preStatement.setTimestamp(8, new java.sql.Timestamp(new java.util.Date().getTime()));
			
			preStatement.executeUpdate();
		}
		catch (DAOException e) 
		{
			throw e;
		} 
		catch (SQLException e) 
		{
			e.printStackTrace();
			throw new DAOException("The insert method failed due to some Query error: " + e.getMessage(), e);
		}
		finally
		{
			MySqlDAOFactory.getInstance().closeConnection();
		}
	}

	@Override
	public void update(Criterion item) throws DAOException 
	{
		String query = "SELECT * FROM " + MySqlDAOFactory.DBName + '.' + TABLE_NAME + " WHERE crt_id = '" + item.getID().toString() + "';";

		try 
		{
			Statement statement = MySqlDAOFactory.getInstance().createStatement();
			ResultSet dataSet = statement.executeQuery(query);
			
			if(dataSet.next())
			{
				String updateQuery = "UPDATE " + TABLE_NAME + " set " +
						"crt_name = ?, crt_Description = ?, crt_goal = ?, crt_SolutionImpact = ?, crt_reference = ?, crt_lastUpdated = ? WHERE crt_id = ?;";
				
				PreparedStatement preStatement = MySqlDAOFactory.getInstance().prepareStatement(updateQuery);
				preStatement.setString(1, item.getName());
				preStatement.setString(2, item.getDescription());
				preStatement.setString(3, item.getGoal());
				preStatement.setString(4, item.getSolutionImpact());
				preStatement.setString(5, item.getReference());
				preStatement.setTimestamp(6, new java.sql.Timestamp(new java.util.Date().getTime()));
				preStatement.setString(7, item.getID().toString());
				
				preStatement.executeUpdate();
					
				if(dataSet.next())
					System.err.println("WARING: Two or more " + TABLE_NAME + " with the same ID were found in the database. Sol_id = " + item.getID());
			}
			else
				throw new DAOException("The " + TABLE_NAME + " identified by the id " + item.getID() + " hasn't been found in the database.");
		}
		catch (DAOException e) 
		{
			throw e;
		} 
		catch (SQLException e) 
		{
			e.printStackTrace();
			throw new DAOException("The update method failed due to some Query error: " + e.getMessage(), e);
		}
		finally
		{
			MySqlDAOFactory.getInstance().closeConnection();
		}
		
	}

	@Override
	public void delete(UUID id) throws DAOException 
	{
		String query = "SELECT * FROM " + MySqlDAOFactory.DBName + '.' + TABLE_NAME + " WHERE crt_id = '" + id + "';";

		try 
		{
			Statement statement = MySqlDAOFactory.getInstance().createStatement();
			ResultSet dataSet = statement.executeQuery(query);
			
			if(dataSet.next())
			{
				String deleteQuery = "DELETE FROM " + TABLE_NAME + " WHERE crt_id = '" + id + "'";
				
				statement = MySqlDAOFactory.getInstance().createStatement();
				if(statement.executeUpdate(deleteQuery) == 0)
					throw new DAOException("The " + TABLE_NAME + " identified by the id " + id + " couldn't be deleted due to an unknown error.");
					
				if(dataSet.next())
					System.err.println("WARING: Two or more " + TABLE_NAME + " with the same ID were found in the database. Crt_id = " + id);
			}
			else
				throw new DAOException("The " + TABLE_NAME + " identified by the id " + id + " hasn't been found in the database.");
		}
		catch (DAOException e) 
		{
			throw e;
		} 
		catch (SQLException e) 
		{
			e.printStackTrace();
			throw new DAOException("The delete method failed due to some Query error: " + e.getMessage(), e);
		}
		finally
		{
			MySqlDAOFactory.getInstance().closeConnection();
		}
	}

	@Override
	public List<Criterion> getAll() throws DAOException
	{
		String query = "SELECT * FROM " + MySqlDAOFactory.DBName + '.' + TABLE_NAME + ";";
		List<Criterion> criteria = new LinkedList<Criterion>();
		boolean lockOwner = false;

		try 
		{
			lockOwner = MySqlDAOFactory.lockConnection.tryLock();
			Statement statement = MySqlDAOFactory.getInstance().createStatement();
			ResultSet dataSet = statement.executeQuery(query);
			
			while(dataSet.next())
			{
				UUID crtID = UUID.fromString(dataSet.getString("crt_id"));
				Criterion crt = getByID(crtID);
				criteria.add(crt);
			}
			
		}
		catch (DAOException e) 
		{
			throw e;
		} 
		catch (SQLException e) 
		{
			e.printStackTrace();
			throw new DAOException("The getAll method failed due to some Query error: " + e.getMessage(), e);
		}
		finally
		{
			if(lockOwner)
			{
				MySqlDAOFactory.lockConnection.unlock();
				MySqlDAOFactory.getInstance().closeConnection();
			}
		}
		
		return criteria;
	}
	
	@Override
	public List<Criterion> getCriteriaByFilters(String search, DateRange dtRange) throws DAOException 
	{
		String query = "SELECT crt_id FROM " + TABLE_NAME;
		List<Criterion> criteria = new LinkedList<Criterion>();
		boolean lockOwner = false;

		try 
		{
			query += MySqlDAOFactory.setUpFilters(ComponentType.CRITERION, search, dtRange);
			
			lockOwner = MySqlDAOFactory.lockConnection.tryLock();
			Statement statement = MySqlDAOFactory.getInstance().createStatement();
			ResultSet dataSet = statement.executeQuery(query);
			
			while(dataSet.next())
			{
				UUID crtID = UUID.fromString(dataSet.getString("crt_id"));
				Criterion crt = getByID(crtID);
				criteria.add(crt);
			}
		}
		catch (DAOException e) 
		{
			throw e;
		} 
		catch (SQLException e) 
		{
			e.printStackTrace();
			throw new DAOException("The getAll method failed due to some Query error: " + e.getMessage(), e);
		}
		finally
		{
			if(lockOwner)
			{
				MySqlDAOFactory.lockConnection.unlock();
				MySqlDAOFactory.getInstance().closeConnection();
			}
		}
		
		return criteria;
	}

	@Override
	public Criterion getByID(UUID id) throws DAOException 
	{
		Criterion crt = null;
		
		if(id != null)
		{
			String query = "SELECT * FROM " + MySqlDAOFactory.DBName + '.' + TABLE_NAME + " WHERE crt_id = '" + id.toString() + "';";

			try 
			{
				Statement statement = MySqlDAOFactory.getInstance().createStatement();
				ResultSet dataSet = statement.executeQuery(query);
				
				if(dataSet.next())
				{
					String name = dataSet.getString("crt_name");
					String description = dataSet.getString("crt_description");
					String goal = dataSet.getString("crt_goal");
					String reference = dataSet.getString("crt_reference");
					String solutionImpact = dataSet.getString("crt_SolutionImpact");
					Date createdDate = new Date(dataSet.getTimestamp("crt_createdDate").getTime());
					Date updatedDate = new Date(dataSet.getTimestamp("crt_lastUpdated").getTime());
					
					crt = new Criterion(name, id);
					crt.setDescription(description);
					crt.setGoal(goal);
					crt.setReference(reference);
					crt.setSolutionImpact(solutionImpact);
					crt.setCreatedDate(createdDate);
					crt.setLastUpdated(updatedDate);
					
					if(dataSet.next())
						System.err.println("WARING: Two or more " + TABLE_NAME + " with the same ID were found in the database. Crt_id = " + id);
				}
				else
					throw new DAOException("The " + TABLE_NAME + " identified by the id " + id + " hasn't been found in the database.");
			}
			catch (DAOException e) 
			{
				throw e;
			} 
			catch (SQLException e) 
			{
				e.printStackTrace();
				throw new DAOException("The getByID method failed due to some Query error: " + e.getMessage(), e);
			}
			finally
			{
				MySqlDAOFactory.getInstance().closeConnection();
			}
		}
		
		return crt;
	}

	@Override
	public List<Criterion> getItemsByIDs(List<UUID> IDs) throws DAOException
	{
		String query = "SELECT * FROM " + MySqlDAOFactory.DBName + '.' + TABLE_NAME + " WHERE whereClause;";
		List<Criterion> criteria = new LinkedList<Criterion>();

		try 
		{
			String whereClause = "";
			for(UUID id : IDs)
			{
				whereClause += "crt_id ='crtID' or ";
				whereClause = whereClause.replace("crtID", id.toString());
			}
			whereClause = whereClause.substring(0, whereClause.length()-3); //Removes the last unnecessary 'or'
			query = query.replace("whereClause", whereClause);
			
			Statement statement = MySqlDAOFactory.getInstance().createStatement();
			ResultSet dataSet = statement.executeQuery(query);
			
			while(dataSet.next())
			{
				UUID crtID = UUID.fromString(dataSet.getString("crt_id"));
				Criterion crt = getByID(crtID);
				criteria.add(crt);
			}
			
		}
		catch (DAOException e) 
		{
			throw e;
		} 
		catch (SQLException e) 
		{
			e.printStackTrace();
			throw new DAOException("The getAll method failed due to some Query error: " + e.getMessage(), e);
		}
		finally
		{
			MySqlDAOFactory.getInstance().closeConnection();
		}
		
		return criteria;
	}
}
